package org.sasha.roulette.data;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;

import javax.inject.Singleton;

import org.sasha.roulette.SessionContext;

import com.vaadin.data.Container;
import com.vaadin.data.Container.Filter;
import com.vaadin.data.util.filter.Compare;
import com.vaadin.data.util.filter.Compare.Equal;
import com.vaadin.data.util.sqlcontainer.SQLContainer;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.FreeformQuery;
import com.vaadin.data.util.sqlcontainer.query.OrderBy;
import com.vaadin.data.util.sqlcontainer.query.TableQuery;
import com.vaadin.data.util.sqlcontainer.query.generator.StatementHelper;
import com.vaadin.data.util.sqlcontainer.query.generator.filter.QueryBuilder;

@Singleton
@SuppressWarnings("serial")
public class DBHelper implements Serializable {

    private static final String DRIVER_NAME = "org.hsqldb.jdbcDriver";
    private static final String connectionUri = "jdbc:hsqldb:hsql://localhost/roulette";
    private static final String userName = "SA";
    private static final String password = "";

    private final String INSERT_SQL = "insert into spin (time, value, session_id) values (?,?,?);";
    
    private final String CREATE_SESSION_SQL = "insert into session (creation_time) values (?);";

    private final String VALUE_OCCURENCE_SQL = "select value, count(value) as occurence from spin ";

    private JDBCConnectionPool pool = null;

    public DBHelper() {
        try {
            pool = new SimpleJDBCConnectionPool(DRIVER_NAME, connectionUri, userName, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void saveSpin(final Spin spin) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = pool.reserveConnection();
            pstmt = conn.prepareStatement(INSERT_SQL);
            pstmt.setDate(1, new java.sql.Date(spin.getTimestamp().getTime()));
            pstmt.setInt(2, spin.getValue());
            pstmt.setInt(3, SessionContext.get().getSessionId());
            pstmt.executeUpdate();
            pstmt.close();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                pool.releaseConnection(conn);
            }
        }
    }

    public int getLatestSessionID() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = pool.reserveConnection();
            pstmt = conn.prepareStatement("select top 1 id from session order by ID desc");
            final ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return ((Long)rs.getLong(1)).intValue();
            } else {
                createNewSession();
            }
            pstmt.close();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                pool.releaseConnection(conn);
            }
        }
        return 0;
    }
    
    public SQLContainer getOccurenceContainer() {
        try {
            final FreeformQuery q = new FreeformQuery("1", pool, "VALUE");
            q.setDelegate(new StatementDelegateAdapter() {
                
                private List<Filter> filters;
                
                @Override
                public void setOrderBy(List<OrderBy> orderBys) throws UnsupportedOperationException {
                }
                
                @Override
                public void setFilters(List<Filter> filters) throws UnsupportedOperationException {
                    this.filters = filters;
                }
                
                @Override
                public StatementHelper getQueryStatement(int offset, int limit) throws UnsupportedOperationException {
                    final StatementHelper stmt = new StatementHelper();
                    final StringBuilder sb = new StringBuilder(VALUE_OCCURENCE_SQL);
                    applyFilters(sb, stmt);
                    sb.append(" group by value");
                    stmt.setQueryString(sb.toString());
                    return stmt;
                }

                private void applyFilters(StringBuilder sb, final StatementHelper stmt) {
                    if (filters != null && !filters.isEmpty()) {
                        sb.append("where ");
                        for (final Filter filter : filters) {
                            if (filter instanceof Compare) {
                                sb.append(QueryBuilder.getWhereStringForFilter(filter, stmt));
                            }
                        }   
                    }
                }
                
                @Override
                public StatementHelper getCountStatement() throws UnsupportedOperationException {
                    final StatementHelper stmt = new StatementHelper();
                    final StringBuilder sb = new StringBuilder("select count(distinct value) from spin ");
                    applyFilters(sb, stmt);
                    stmt.setQueryString(sb.toString());
                    return stmt;
                }
                
            });
            final SQLContainer c = new SQLContainer(q);
            c.addContainerFilter(new Equal("SESSION_ID", SessionContext.get().getSessionId()));
            return c;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public SQLContainer getSpinContainer() {
        try {
            final SQLContainer c = new SQLContainer(new TableQuery("SPIN", pool));
            c.sort(new Object[] { "TIME" }, new boolean[] { false });
            c.addContainerFilter(new Equal("SESSION_ID", SessionContext.get().getSessionId()));
            return c;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int createNewSession() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = -1;
        try {
            conn = pool.reserveConnection();
            pstmt = conn.prepareStatement(CREATE_SESSION_SQL, Statement.RETURN_GENERATED_KEYS);
            pstmt.setDate(1, new java.sql.Date(new Date().getTime()));
            pstmt.executeUpdate();
            final ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                result = ((Long)rs.getLong(1)).intValue();
            } else {
                throw new SQLException("Creating session failed, no rows affected.");
            }
            pstmt.close();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                pool.releaseConnection(conn);
            }
        }
        return result;
    }

    public Container getSessionContainer() {
        try {
            final SQLContainer c = new SQLContainer(new TableQuery("SESSION", pool));
            c.sort(new Object[]{"CREATION_TIME"}, new boolean[]{false});
            return c;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
